/**
 * Copyright (C) 2008 ChangSha WangWin Science & Technology CO,.LTD. All rights reserved.
 * WangWin PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
 */

/**
 * Copyright (C) 2002-2008 WUZEWEN. All rights reserved.
 * WUZEWEN PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
 *
 * This software is the confidential and proprietary information of
 * WuZeven, Personal. ("Confidential Information").  You shall not
 * disclose such Confidential Information and shall use it only in
 * accordance with the terms of the license agreement you entered into
 * with WuZeven.
 */

package com.kingcore.pda.database.bean.impl;

import java.util.HashMap;

import org.apache.log4j.Logger;

import com.kingcore.pda.database.bean.DatabaseDictionary;

/**
 * <p>MySql 数据字典对象。</p>
 * @author Zeven on 2008-11-25
 * @version	1.0
 * @see		Object#equals(java.lang.Object)
 * @see		Object#hashCode()
 * @see		HashMap
 * @since	JDK5
 */

public class MySqlDatabaseDictionary implements DatabaseDictionary {

	private final static Logger log = Logger.getLogger(MySqlDatabaseDictionary.class);
	
	/**
	 * <p>java方法的说明...</p>
	 * @author Zeven on 2008-11-25
	 * @param args
	 */
	public static void main(String[] args) {

	}

	/**
	 * 根据条件，获取所有符合条件的表的所有信息。
	 */
	public String createSqlOfTableInforByCondition(String tableCondition) {
		StringBuffer sbf=new StringBuffer();
		//sbf.delete(0, sbf.length());
		sbf.append(" Select 1 As Tblid, Table_Name, 'TABLE' As Table_Type")
			.append(" , Table_Comment As Comments")
			.append(" From Information_Schema.Tables t")
			.append(" Where ("+tableCondition+")")
			.append(" Order By t.Table_Name");
		log.debug(sbf);
		//String sql="SELECT 1 AS tblId, TABLE_NAME, 'TABLE' AS TABLE_TYPE, TABLE_COMMENT AS COMMENTS FROM information_schema.TABLES t where ("+tableCondition+")  order by t.TABLE_NAME" ;
		return sbf.toString();
	}


	/**
	 * 根据表名，获取表的所有信息。
	 */
	public String createSqlOfTableInforByName(String dbName, String tableName ) {
		StringBuffer sbf=new StringBuffer();
		//sbf.delete(0, sbf.length());
		sbf.append(" Select 1 As Tblid, Table_Name, 'TABLE' As Table_Type")
			.append(" , Table_Comment As Comments")
			.append(" From Information_Schema.Tables t")
			.append(" Where t.Table_Name = '"+tableName+"'")
			.append(" And t.Table_Schema = '"+dbName+"'");
		log.debug(sbf);
		//String sql="SELECT 1 AS tblId, TABLE_NAME, 'TABLE' AS TABLE_TYPE, TABLE_COMMENT AS COMMENTS FROM information_schema.TABLES t where t.TABLE_NAME='"+tableName+"' and t.TABLE_SCHEMA='"+dbName+"'" ;
		return sbf.toString();
	}

	/**
	 * 获取表的所有的列信息。
	 */
	public String createSqlOfColumnInforByCondition(String dbName, String tableId, String tableEn) {
		StringBuffer sbf=new StringBuffer();
		//sbf.delete(0, sbf.length());  //CHARACTER_OCTET_LENGTH->CHARACTER_MAXIMUM_LENGTH
		sbf.append(" select t.COLUMN_NAME,t.DATA_TYPE,t.CHARACTER_MAXIMUM_LENGTH as DATA_LENGTH")
			.append(" ,t.NUMERIC_PRECISION as DATA_PRECISION,t.NUMERIC_SCALE as DATA_SCALE")
			.append(" ,case t.IS_NULLABLE when 'YES' then 'Y' else 'N' end as NULLABLE")
			.append(" ,t.ORDINAL_POSITION as COLUMN_ID,t.COLUMN_COMMENT as comments")
			.append(" ,INSTR(t.extra,'auto_increment') as isAutoIncrement")
			.append(" ,t.Column_Type as typefull")   //Column_Type is good
//			.append(" ,case t.DATA_TYPE when 'varchar' then CONCAT(t.DATA_TYPE,'(',t.CHARACTER_MAXIMUM_LENGTH ,')')")
//			.append(" when 'char' then CONCAT(t.DATA_TYPE , '(' , t.CHARACTER_MAXIMUM_LENGTH , ')')")
//			.append(" when 'NUMBER' then 'NUMBER' else t.DATA_TYPE end typefull")
			.append(" from information_schema.COLUMNS t")
			.append(" where t.table_name='"+tableEn+"'")
			.append(" and t.TABLE_SCHEMA='"+dbName+"'")
			.append(" order by ORDINAL_POSITION asc");
		log.debug(sbf);
		return sbf.toString();
		//return "select t.COLUMN_NAME,t.DATA_TYPE,t.CHARACTER_MAXIMUM_LENGTH as DATA_LENGTH,t.NUMERIC_PRECISION as DATA_PRECISION,t.NUMERIC_SCALE as DATA_SCALE, case t.IS_NULLABLE when 'YES' then 'Y' else 'N' end as NULLABLE, t.ORDINAL_POSITION as COLUMN_ID,t.COLUMN_COMMENT as comments, case t.DATA_TYPE when 'varchar' then CONCAT(t.DATA_TYPE,'(',t.CHARACTER_MAXIMUM_LENGTH ,')') when 'char' then CONCAT(t.DATA_TYPE , '(' , t.CHARACTER_MAXIMUM_LENGTH , ')') when 'NUMBER' then 'NUMBER' else t.DATA_TYPE end typefull from information_schema.COLUMNS t where t.table_name='"+tableEn+"' and t.TABLE_SCHEMA='"+dbName+"' order by ORDINAL_POSITION asc";
	}

	/**
	 * 获取某个表的约束的集合。
	 */
	public String createSqlOfConstraints(String table_schema, String table_name) {
		StringBuffer sbf=new StringBuffer();
		//sbf.delete(0, sbf.length());
		sbf.append(" SELECT t1.CONSTRAINT_CATALOG,t1.CONSTRAINT_SCHEMA")
			.append(" ,t1.CONSTRAINT_NAME,t1.TABLE_SCHEMA,t1.TABLE_NAME,t1.CONSTRAINT_TYPE")
			.append(" FROM information_schema.TABLE_CONSTRAINTS t1")
			.append(" Where LOWER(t1.table_schema)='"+table_schema.toLowerCase()+"'")
			.append(" And LOWER(t1.table_name)='"+table_name.toLowerCase()+"'");
			//.append(" Order by t1.CONSTRAINT_CATALOG desc");
		log.debug(sbf);
		return sbf.toString();
	}
	
	/**
	 * 获取表的某个约束的列信息（主键只有一个）。 
	 */
	public String createSqlOfConstraintColumns(String table_schema, String table_name
						, String constraint_name) {
		StringBuffer sbf=new StringBuffer();
		//sbf.delete(0, sbf.length());
		sbf.append(" SELECT t1.CONSTRAINT_CATALOG,t1.CONSTRAINT_SCHEMA")
			.append(" ,t1.CONSTRAINT_NAME,t1.TABLE_CATALOG,t1.TABLE_SCHEMA")
			.append(" ,t1.TABLE_NAME,t1.COLUMN_NAME,t1.ORDINAL_POSITION")
			.append(" ,t1.POSITION_IN_UNIQUE_CONSTRAINT,t1.REFERENCED_TABLE_SCHEMA")
			.append(" ,t1.REFERENCED_TABLE_NAME,t1.REFERENCED_COLUMN_NAME")
			.append(" FROM information_schema.KEY_COLUMN_USAGE t1")
			.append(" Where LOWER(t1.table_schema)='"+table_schema.toLowerCase()+"'")
			.append(" And LOWER(t1.table_name)='"+table_name.toLowerCase()+"'")
			.append(" And LOWER(t1.CONSTRAINT_NAME)='"+constraint_name.toLowerCase()+"'")
			.append(" Order by t1.CONSTRAINT_CATALOG desc");
		log.debug(sbf); 
		return sbf.toString();
	}

	//ALTER TABLE `table_name` COMMENT'注释';  可以为表添加注释
	public String createUpdateCommentSql(String dbName, String tableEn, String colEn, String comment) {
		StringBuffer sbf=new StringBuffer(); 
		sbf.append("ALTER TABLE "+tableEn+" CHANGE ")
				.append(" COLUMN "+colEn+" "+colEn+" " +comment );		 
		log.debug(sbf);
		return sbf.toString();}

}
